using SPI.AgentServer.Services.Execution;
using System.Text;

namespace SPI.AgentServer.Services.Tools;

/// <summary>
/// 报警统计查询工具 - 查询设备报警统计信息
/// 覆盖场景：D-004 今天报警统计, D-009 未处理报警, D-010 特定类型设备报警
/// </summary>
public class GetAlarmStatsTool : IQueryTool
{
    private readonly IDatabaseExecutor _executor;
    private readonly ILogger<GetAlarmStatsTool> _logger;

    public string Name => "get_alarm_stats";
    public string Description => "查询设备报警统计信息，支持按时间范围、设备类型、处理状态筛选。用于回答'今天有多少报警'、'未处理的报警'、'某类型设备报警情况'等问题。";

    public GetAlarmStatsTool(
        IDatabaseExecutor executor,
        ILogger<GetAlarmStatsTool> logger)
    {
        _executor = executor;
        _logger = logger;
    }

    public bool ValidateParameters(Dictionary<string, object> parameters, out List<string> errors)
    {
        errors = new List<string>();
        return true;
    }

    public async Task<ToolResult> ExecuteAsync(Dictionary<string, object> parameters, CancellationToken cancellationToken = default)
    {
        if (parameters == null)
        {
            return new ToolResult
            {
                Success = false,
                Errors = new List<string> { "参数不能为空" }
            };
        }

        try
        {
            // 获取参数
            string? startTime = parameters.ContainsKey("startTime") ? parameters["startTime"]?.ToString() : null;
            string? endTime = parameters.ContainsKey("endTime") ? parameters["endTime"]?.ToString() : null;
            string? deviceType = parameters.ContainsKey("deviceType") ? parameters["deviceType"]?.ToString() : null;
            int? operateStatus = parameters.ContainsKey("operateStatus") ? Convert.ToInt32(parameters["operateStatus"]) : null;
            bool summaryOnly = parameters.ContainsKey("summaryOnly") && Convert.ToBoolean(parameters["summaryOnly"]);

            // 默认查询今天
            if (string.IsNullOrEmpty(startTime))
            {
                startTime = DateTime.Today.ToString("yyyy-MM-dd");
            }

            // 构建SQL
            var sql = summaryOnly
                ? BuildSummaryQuery(startTime, endTime)
                : BuildDetailQuery(startTime, endTime, deviceType, operateStatus);

            _logger.LogInformation("Executing alarm stats query with SQL: {Sql}", sql);

            var result = await _executor.ExecuteQueryAsync(sql, null, cancellationToken);

            return new ToolResult
            {
                Success = true,
                Data = result.Data,
                Message = "报警统计查询成功",
                Metadata = new Dictionary<string, object>
                {
                    ["executionTimeMs"] = result.ExecutionTimeMs,
                    ["executedSql"] = sql
                }
            };
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error executing alarm stats query");
            return new ToolResult
            {
                Success = false,
                Errors = new List<string> { $"查询失败: {ex.Message}" }
            };
        }
    }

    /// <summary>
    /// 构建汇总统计查询
    /// </summary>
    private string BuildSummaryQuery(string startTime, string? endTime)
    {
        var sb = new StringBuilder();

        sb.AppendLine("SELECT");
        sb.AppendLine("    COUNT(*) AS total_alarms,");
        sb.AppendLine("    COUNT(DISTINCT DeviceId) AS affected_devices,");
        sb.AppendLine("    SUM(CASE WHEN OperateStatus IN (1) THEN 1 ELSE 0 END) AS pending_alarms,");
        sb.AppendLine("    SUM(CASE WHEN EventType = 2 THEN 1 ELSE 0 END) AS offline_alarms,");
        sb.AppendLine("    SUM(CASE WHEN EventType = 19003 THEN 1 ELSE 0 END) AS speeding_alarms,");
        sb.AppendLine("    SUM(CASE WHEN EventType = 5002 THEN 1 ELSE 0 END) AS intrusion_alarms,");
        sb.AppendLine("    SUM(CASE WHEN EventType = 20001 THEN 1 ELSE 0 END) AS smoke_alarms,");
        sb.AppendLine("    MIN(AlarmCreateTime) AS first_alarm_time,");
        sb.AppendLine("    MAX(AlarmCreateTime) AS last_alarm_time");
        sb.AppendLine("FROM TblAlarmLog");
        sb.AppendLine("WHERE DATE(AlarmCreateTime) >= DATE('" + startTime + "')");

        if (!string.IsNullOrEmpty(endTime))
        {
            sb.AppendLine("    AND DATE(AlarmCreateTime) <= DATE('" + endTime + "')");
        }

        return sb.ToString();
    }

    /// <summary>
    /// 构建详细报警查询
    /// </summary>
    private string BuildDetailQuery(string startTime, string? endTime, string? deviceType, int? operateStatus)
    {
        var sb = new StringBuilder();

        sb.AppendLine("SELECT");
        sb.AppendLine("    al.ID AS alarm_id,");
        sb.AppendLine("    d.Name AS device_name,");
        sb.AppendLine("    d.CustomCode AS device_code,");
        sb.AppendLine("    dt.Name AS device_type,");
        sb.AppendLine("    a.Name AS area_name,");
        sb.AppendLine("    CASE al.EventType");
        sb.AppendLine("        WHEN 2 THEN '设备下线'");
        sb.AppendLine("        WHEN 19003 THEN 'AI报警-车辆超速'");
        sb.AppendLine("        WHEN 5002 THEN 'AI报警-区域入侵'");
        sb.AppendLine("        WHEN 5013 THEN 'AI报警-人员密度'");
        sb.AppendLine("        WHEN 40001 THEN '校园防欺凌报警-AI语音告警'");
        sb.AppendLine("        WHEN 20001 THEN '烟感报警'");
        sb.AppendLine("        ELSE CONCAT('事件类型:', al.EventType)");
        sb.AppendLine("    END AS event_type,");
        sb.AppendLine("    al.Name AS alarm_name,");
        sb.AppendLine("    al.AlarmName AS alarm_display_name,");
        sb.AppendLine("    DATE_FORMAT(al.AlarmCreateTime, '%Y-%m-%d %H:%i:%s') AS alarm_time,");
        sb.AppendLine("    TIMESTAMPDIFF(HOUR, al.AlarmCreateTime, NOW()) AS pending_hours,");
        sb.AppendLine("    al.AlarmLevel AS alarm_level,");
        sb.AppendLine("    CASE al.OperateStatus");
        sb.AppendLine("        WHEN 1 THEN '待处理'");
        sb.AppendLine("        WHEN 2 THEN '处理中'");
        sb.AppendLine("        WHEN 3 THEN '已处理'");
        sb.AppendLine("        ELSE '未知'");
        sb.AppendLine("    END AS operate_status");
        sb.AppendLine("FROM TblAlarmLog al");
        sb.AppendLine("INNER JOIN TblDevice d ON al.DeviceId = d.ID");
        sb.AppendLine("LEFT JOIN TblDeviceType dt ON d.DeviceTypeId = dt.ID");
        sb.AppendLine("LEFT JOIN TblArea a ON d.StationAreaId = a.ID");
        sb.AppendLine("WHERE DATE(al.AlarmCreateTime) >= DATE('" + startTime + "')");

        if (!string.IsNullOrEmpty(endTime))
        {
            sb.AppendLine("    AND DATE(al.AlarmCreateTime) <= DATE('" + endTime + "')");
        }

        if (operateStatus.HasValue)
        {
            sb.AppendLine($"    AND al.OperateStatus = {operateStatus.Value}");
        }

        if (!string.IsNullOrEmpty(deviceType))
        {
            sb.AppendLine($"    AND dt.Name LIKE '%{deviceType}%'");
        }

        sb.AppendLine("ORDER BY al.AlarmCreateTime DESC");
        sb.AppendLine("LIMIT 50");

        return sb.ToString();
    }
}
